package cn.flant.excel.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.flant.excel.ExcelHandler;
import cn.flant.io.IOUtil;

public class POIExcelHandler implements ExcelHandler {

	private boolean isXlsxFile(File file) {
		return file.getName().toLowerCase().endsWith(".xlsx");
	}

	@Override
	public List<String[]> getSheetData(File file, int sheetId) throws Exception {
		InputStream input = null;
		try {
			input = new FileInputStream(file);
			Workbook wb = null;
			if (isXlsxFile(file)) {
				wb = new XSSFWorkbook(input);
			} else {
				wb = new HSSFWorkbook(input);
			}
			Sheet sheet = wb.getSheetAt(sheetId);
			if (sheet == null) {
				throw new Exception("Sheet not found by id: " + sheetId);
			}
			return sheetToData(sheet);
		} catch (Throwable e) {
			if (e instanceof Exception) {
				throw (Exception) e;
			} else {
				throw new Exception("read sheet data error: " + e.getMessage(),
						e);
			}
		} finally {
			IOUtil.close(input);
		}
	}

	@Override
	public Map<String, List<String[]>> getAllData(File file) throws Exception {
		InputStream input = null;
		try {
			input = new FileInputStream(file);
			Workbook wb = null;
			if (isXlsxFile(file)) {
				wb = new XSSFWorkbook(input);
			} else {
				wb = new HSSFWorkbook(input);
			}
			int sheetNums = wb.getNumberOfSheets();
			Map<String, List<String[]>> dataMap = new HashMap<String, List<String[]>>();
			for (int i = 0; i < sheetNums; i++) {
				String sheetName = wb.getSheetName(i);
				Sheet sheet = wb.getSheetAt(i);
				dataMap.put(sheetName, sheetToData(sheet));
			}
			return dataMap;
		} catch (Throwable e) {
			if (e instanceof Exception) {
				throw (Exception) e;
			} else {
				throw new Exception("read sheet data error: " + e.getMessage(),
						e);
			}
		} finally {
			IOUtil.close(input);
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	private List<String[]> sheetToData(Sheet sheet) {
		if (sheet.getPhysicalNumberOfRows() == 0) {
			return new ArrayList<String[]>();
		}
		int rowCount = sheet.getLastRowNum() + 1;
		List dataList = new ArrayList(rowCount);
		int maxColCount = 0;
		for (int i = 0; i < rowCount; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				dataList.add(null);
				continue;
			}
			int colCount = row.getLastCellNum();
			if (maxColCount < colCount) {
				maxColCount = colCount;
			}
			List<String> data = new ArrayList<String>();
			for (int j = 0; j < colCount; j++) {
				data.add(getCellValue(row.getCell(j)));
			}
			dataList.add(data);
		}
		String[] blankRow = null;
		for (int i = 0; i < dataList.size(); i++) {
			Object obj = dataList.get(i);
			if (obj == null) {
				if (blankRow == null) {
					blankRow = new String[maxColCount];
					fillArray(blankRow, 0, blankRow.length, "");
				}
				dataList.set(i, blankRow);
			} else {
				List<String> data = (List<String>) obj;
				String[] row = data.toArray(new String[maxColCount]);
				if (data.size() < row.length) {
					fillArray(row, data.size(), row.length, "");
				}
				dataList.set(i, row);
			}
		}
		return dataList;
	}

	private void fillArray(String[] a, int from, int to, String str) {
		for (int i = from; i < to; i++) {
			a[i] = str;
		}
	}

	private String getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_FORMULA:
			return String.valueOf(cell.getCellFormula()).trim();
		case Cell.CELL_TYPE_NUMERIC: {
			if (cell.getCellStyle().getDataFormat() > 0) {
				return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
			} else {
				return String.valueOf(cell.getNumericCellValue());
			}
		}
		case Cell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString().trim();
		default:
			return cell.toString().trim();
		}
	}
}
